package br.org.acessobrasil.portal.persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import javax.persistence.Query;

import org.springframework.transaction.annotation.Transactional;

import br.org.acessobrasil.facade.FacadeException;
import br.org.acessobrasil.portal.modelo.ItemMenu;
import br.org.acessobrasil.portal.modelo.Pagina;
import br.org.acessobrasil.portal.modelo.Usuario;
import br.org.acessobrasil.portal.util.MySqlConnection;

@Transactional
public class ItemMenuDaoSpring extends SuperDao<ItemMenu>{

	private static ItemMenuDaoSpring instance;
	public ItemMenuDaoSpring(){
		instance = this;
	}
	
	/**
	 * Implementado
	 */
	public void apagar(Long nu_item_menu) {
		/*
		ItemMenu item = select(nu_item_menu);
		getEntityManager().remove(item);
		*/
		try {
			Connection con = MySqlConnection.getConnection();
			PreparedStatement ps = con.prepareStatement("Delete From sgctb_item_menu where nu_item_menu = ?");
			ps.setLong(1, nu_item_menu);
			ps.execute();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * Implementado
	 */
	public void desceItem(long nu_item_menu) {
		try {
			int ordem, ordem_acima, nu_item_menu_acima, nu_menu;
			Connection con = MySqlConnection.getConnection();
			PreparedStatement ps = con.prepareStatement("Select nu_ordem,nu_menu From sgctb_item_menu where nu_item_menu = ?");
			ps.setLong(1, nu_item_menu);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				ordem = rs.getInt("nu_ordem");
				nu_menu = rs.getInt("nu_menu");
				rs.close();
				ps.close();
				ps = con.prepareStatement("Select nu_ordem,nu_item_menu from sgctb_item_menu where nu_ordem>? and nu_menu=? order by nu_ordem Limit 1");
				ps.setInt(1, ordem);
				ps.setInt(2, nu_menu);
				rs = ps.executeQuery();
				if (rs.next()) {
					ordem_acima = rs.getInt("nu_ordem");
					nu_item_menu_acima = rs.getInt("nu_item_menu");
					ps.close();
					ps = con.prepareStatement("Update sgctb_item_menu Set nu_ordem=? where nu_item_menu=?");
					ps.setInt(1, ordem);
					ps.setInt(2, nu_item_menu_acima);
					ps.execute();
					ps.close();
					ps = con.prepareStatement("Update sgctb_item_menu Set nu_ordem=? where nu_item_menu=?");
					ps.setInt(1, ordem_acima);
					ps.setLong(2, nu_item_menu);
					ps.execute();
					ps.close();
				}
			}
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unchecked")
	
	public List<ItemMenu> getListItens(Long nu_menu) {
		Query q =getEntityManager().createQuery("Select im from ItemMenu im where im.nu_menu=? order by im.nu_ordem");
		q.setParameter(1,nu_menu);
		return q.getResultList();
	}

	/**
	 * implementado
	 */
	public void moveDireita(long nu_item_menu) {
		try {
			Connection con = MySqlConnection.getConnection();
			PreparedStatement ps = con.prepareStatement("Update sgctb_item_menu set nu_nivel=nu_nivel+1 where nu_item_menu = ?");
			ps.setLong(1, nu_item_menu);
			ps.execute();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	/**
	 * Implementado
	 */	
	public void moveEsquerda(long nu_item_menu) {
		Connection con=null;
		try {
			con = MySqlConnection.getConnection();
			PreparedStatement ps = con.prepareStatement("Update sgctb_item_menu set nu_nivel=nu_nivel-1 where nu_nivel>1 and nu_item_menu = ?");
			ps.setLong(1, nu_item_menu);
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}
	
	/**
	 * Implementado
	 */
	public void reordenar(Long nu_menu) {
		//TODO colocar no EM
		try {
			Connection con = MySqlConnection.getConnection();
			PreparedStatement ps = con.prepareStatement("Select * From sgctb_item_menu where nu_menu = ? order by nu_ordem, nu_item_menu");
			ps.setLong(1, nu_menu);
			ResultSet rs = ps.executeQuery();
			int i = 1;
			while (rs.next()) {
				PreparedStatement ps2 = con.prepareStatement("Update sgctb_item_menu set nu_ordem=? where nu_item_menu = ?");
				ps2.setInt(1, i++);
				ps2.setLong(2, rs.getLong("nu_item_menu"));
				ps2.executeUpdate();
			}
			rs.close();
			ps.close();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Implementado
	 */
	public ItemMenu select(long nu_item_menu) {
		return getEntityManager().find(ItemMenu.class,nu_item_menu);
	}
	
	/**
	 * Implementado
	 */
	public void sobeItem(Long nu_item_menu) {
		Connection con = null;
		try {
			int ordem, ordem_acima;
			long nu_menu, nu_item_menu_acima;
			con = MySqlConnection.getConnection();
			PreparedStatement ps = con.prepareStatement("Select nu_ordem,nu_menu From sgctb_item_menu where nu_item_menu = ?");
			ps.setLong(1, nu_item_menu);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				ordem = rs.getInt("nu_ordem");
				nu_menu = rs.getLong("nu_menu");
				rs.close();
				ps.close();
				ps = con.prepareStatement("Select nu_ordem,nu_item_menu from sgctb_item_menu where nu_ordem<? and nu_menu=? order by nu_ordem desc Limit 1");
				ps.setInt(1, ordem);
				ps.setLong(2, nu_menu);
				rs = ps.executeQuery();
				if (rs.next()) {
					ordem_acima = rs.getInt("nu_ordem");
					nu_item_menu_acima = rs.getLong("nu_item_menu");
					ps.close();
					ps = con.prepareStatement("Update sgctb_item_menu Set nu_ordem=? where nu_item_menu=?");
					ps.setInt(1, ordem);
					ps.setLong(2, nu_item_menu_acima);
					ps.execute();
					ps.close();
					ps = con.prepareStatement("Update sgctb_item_menu Set nu_ordem=? where nu_item_menu=?");
					ps.setInt(1, ordem_acima);
					ps.setLong(2, nu_item_menu);
					ps.execute();
					ps.close();
				}
			}		
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}
		}
	}

	@SuppressWarnings("unchecked")
	public List<Pagina> listarPaginasPorItemMenu(Long nuSitio, Long nuItemMenu, boolean decrescente) {
		String ordem = decrescente ? "desc" : "asc";
		Query q = getEntityManager().createQuery("Select p From Pagina p where p.nu_sitio=? and p.nu_item_menu_secao=? order by p.no_titulo " + ordem );
		q.setParameter(1, nuSitio);
		q.setParameter(2, nuItemMenu);
		return q.getResultList();
	}
	
	@SuppressWarnings("unchecked")
	public List<Pagina> listarPaginasPorItemMenu(Long nuSitio, String nomeDoItemMenu, boolean decrescente) {
		String ordem = decrescente ? "desc" : "asc";
		Query q = getEntityManager().createQuery("SELECT p FROM Pagina p, ItemMenu i " +
				"WHERE p.nu_item_menu_secao = i.nu_item_menu AND p.nu_sitio=? AND i.no_titulo=? " +
				"ORDER BY p.no_titulo " + ordem );
		q.setParameter(1, nuSitio);
		q.setParameter(2, nomeDoItemMenu);
		return q.getResultList();
	}
	
	public static ItemMenuDaoSpring getInstance() {
		return instance;
	}
	/**
	 * Salva um item de menu no banco
	 */
	public void create(ItemMenu itemMenu) {
		getEntityManager().persist(itemMenu);
	}

	@Override
	public ItemMenu select(Long id) throws Exception {
		return getEntityManager().find(ItemMenu.class, id);
	}
}
